<?php
class Kdt_goods_model extends Base_Model
{
    const GOODS_TABLE = 'kdt_goods';
    const ORDERS_TABLE = 'kdt_orders';
    const TRADE_TABLE = 'kdt_trades';
    const KDT_SM_TRADE = 'kdt_salesman_trades';
    const KDT_SM_ORDER = 'kdt_salesman_orders';

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    public function getGoodsSales($start,$end)
    {
        $res=$this->db->select('num_iid,SUM(num) as num,SUM(payment) as payment')->from(self::ORDERS_TABLE)
            ->where('status IN (4,5,6,7,8)')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->group_by('num_iid')->get()->result_array();
        $goodids = array();
        $salesdata = array();
        foreach ($res as $row){
            $goodids[] = $row['num_iid'];
            $salesdata[$row['num_iid']]['num'] = (int)$row['num'];
            $salesdata[$row['num_iid']]['payment'] = (int)$row['payment'];
        }
        return array('goodids'=>$goodids,'sales'=>$salesdata);
    }

    public function getOrderPay($start,$end)
    {
        $res=$this->db->select('SUM(payment) as payment')->from(self::ORDERS_TABLE)
            ->where('status IN (4,5,6,7,8)')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->get()->row_array();
        return (int)$res['payment'];
    }

    public function getTradePay($start,$end)
    {
        $res=$this->db->select('SUM(payment) as payment')->from(self::TRADE_TABLE)
            ->where('status IN (4,5,6,7,8)')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->get()->row_array();
        return (int)$res['payment'];
    }

    public function getRefundFee($start,$end)
    {
        //部分退款的订单，不包含全额退款的订单
        $res=$this->db->select('SUM(refundFee) as refundFee')->from(self::TRADE_TABLE)
            ->where('status IN (4,5,6,7,8)')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->get()->row_array();
        return (int)$res['refundFee'];
    }

    public function getPostPay($start,$end)
    {
        $res=$this->db->select('SUM(postFee) as postFee')->from(self::TRADE_TABLE)
            ->where('status IN (4,5,6,7,8)')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->get()->row_array();
        return (int)$res['postFee'];
    }

    public function getOrderDiscount($start,$end)
    {
        $res=$this->db->select('SUM(discountFee) as discountFee')->from(self::ORDERS_TABLE)
            ->where('status IN (4,5,6,7,8)')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->get()->row_array();
        return (int)$res['discountFee'];
    }

    public function getGoodsData()
    {
        $res = $this->db->select('title,num_iid,group,groupName')->from(self::GOODS_TABLE)->order_by('group','ASC')->get()->result_array();
        $goodsdata = array();
        if (!empty($res)){
            foreach ($res as $row){
                $goodsdata[$row['group']]['name']=$row['groupName'];
                $goodsdata[$row['group']]['goods'][] = array(
                    'num_iid'=>$row['num_iid'],
                    'title'=>$row['title']
                    );
            }
        }
        return array_values($goodsdata);
    }

    public function getGoodsName()
    {
        $res = $this->db->select('groupName')->from(self::GOODS_TABLE)->group_by(array("group","groupName"))->order_by('group','ASC')->get()->result_array();
        $goodsname = array();
        if (!empty($res)){
            foreach ($res as $row){
                $goodsname[]=$row['groupName'];
            }
        }
        return $goodsname;
    }

    public function getTradesNum($start,$end)
    {
        $res=$this->db->select('count(*) as trades')->from(self::TRADE_TABLE)
            ->where('status IN (4,5,6,7,8)')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->get()->row_array();

        return (int)$res['trades'];
    }

    public function formatGSExcel($start,$end)
    {
        $data = $this->getGoodsSales($start,$end);
        $goodsdata = $this->getGoodsData();
        $tradetotalpay = $this->getTradePay($start,$end);//交易实际支付
        $ordertotalpay = $this->getOrderPay($start,$end);//订单实际支付
        $refundtotal = $this->getRefundFee($start,$end);//主动退款总额
        $posttotalpay = $this->getPostPay($start,$end);//邮费总计
        $orderdiscount = $this->getOrderDiscount($start,$end);//单品级优惠
        $tradesnum = $this->getTradesNum($start,$end);//订单数

        $fee = array();
        $totalfee = 0;
        $num = array();

        foreach ($goodsdata as $group){
            $feeitem = 0;
            $numitem = 0;
            foreach ($group['goods'] as $good){
                if (isset($data['sales'][$good['num_iid']])){
                    //产品有售出
                    $feeitem += $data['sales'][$good['num_iid']]['payment'];
                    $totalfee += $data['sales'][$good['num_iid']]['payment'];
                    $numitem += $data['sales'][$good['num_iid']]['num'];
                }
            }
            $fee[] = $feeitem===0?0:(float)$feeitem/100;
            $num[] = $numitem;
        }

        $indivipay = (float)($ordertotalpay-$totalfee)/100;//独立收款(没有在kdt_goods中的num_iid)
        $discount = (float)($ordertotalpay+$posttotalpay-$tradetotalpay)/100;//店铺级优惠总计
        $posttotalpay = (float)$posttotalpay/100;//邮费总计
        $orderdiscount = (float)$orderdiscount/100;//单品级优惠总计
        $tradetotalpay = (float)$tradetotalpay/100;//支付金额
        $refundtotal = (float)$refundtotal/100;//主动退款
        $realpay = $tradetotalpay-$refundtotal;//实际销售额
        $pct = $tradesnum==0?0:round($tradetotalpay/$tradesnum,2);//客单价

        //商品销售额...,独立收款总计,邮费总计,单品级优惠总计,店铺级优惠总计,订单数,客单价,实际支付,退款总额,实际销售额;
        $fee = array_merge($fee,array($indivipay,$posttotalpay,$orderdiscount,$discount,$tradesnum,$pct,$tradetotalpay,$refundtotal,$realpay));
        //商品销量...
        return array("fee"=>$fee,"num"=>$num);
    }

    public function getSMCpsMoney($start,$end)
    {
        $res=$this->db->select('SUM(cps_money) as cps')->from(self::KDT_SM_TRADE)
            ->where('state > 3')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->get()->row_array();
        return (int)$res['cps'];
    }

    public function getSMInviMoney($start,$end)
    {
        //筛选出有邀请奖励的订单
        $res=$this->db->select('t.tid')->from('kdt_salesman_trades as t')
            ->join('kdt_salesman as s','t.idcode=s.idcode','left')
            ->where("s.invifrom != ''")
            ->where('t.state > 3')
            ->where('t.created > '.$start)
            ->where('t.created < '.$end)
            ->get()->result_array();
        if (empty($res)) return array('cps'=>0,'tradenum'=>0,'invitor'=>0);
        //重组订单数组
        $tids = array();
        foreach ($res as $row)
        {
            $tids[] = $row['tid'];
        }
        //计算邀请订单数
        $invitradenum = count($tids);
        //计算邀请成单人数
        $invinum = $this->db->select('idcode')->from(self::KDT_SM_TRADE)
            ->where_in('tid',$tids)
            ->group_by('idcode')
            ->get()->result_array();
        $invinum = count($invinum);
        //计算订单的邀请奖励
        $res=$this->db->select('SUM(ii_cps_money) as cps')->from(self::KDT_SM_ORDER)
            ->where_in('tid',$tids)
            ->get()->row_array();

        return array('cps'=>(int)$res['cps'],'tradenum'=>$invitradenum,'invitor'=>$invinum);
    }

    public function getSMSuccNum($start,$end)
    {
        $res=$this->db->select('idcode')->from(self::KDT_SM_TRADE)
            ->where('state > 3')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->group_by('idcode')
            ->get()->result_array();

        return $res;
    }

    public function getSMTradesNum($start,$end)
    {
        $res=$this->db->select('count(*) as num')->from(self::KDT_SM_TRADE)
            ->where('state > 3')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->get()->row_array();

        return (int)$res['num'];
    }

    public function getSMValidTrades($start,$end)
    {
        $res=$this->db->select('tid')->from(self::KDT_SM_TRADE)
            ->where('state > 3')
            ->where('created > '.$start)
            ->where('created < '.$end)
            ->get()->result_array();
        $tids = array();
        foreach ($res as $row)
        {
            $tids[] = $row['tid'];
        }
        return $tids;
    }

    public function getSMGoodsSales($tids=array())
    {
        //生成产品销量和销售额
        $res=$this->db->select('num_iid,SUM(num) as num,SUM(payment) as payment')->from(self::ORDERS_TABLE)
            ->where_in('tid',$tids)
            ->group_by('num_iid')->get()->result_array();
        return $res;
    }

    public function getSMTradePay($tids=array())
    {
        $res=$this->db->select('SUM(payment) as payment')->from(self::TRADE_TABLE)
            ->where_in('tid',$tids)
            ->get()->row_array();
        return (int)$res['payment'];
    }

    public function getSMRefundFee($tids=array())
    {
        $res=$this->db->select('SUM(refundFee) as refundFee')->from(self::TRADE_TABLE)
            ->where_in('tid',$tids)
            ->get()->row_array();
        return (int)$res['refundFee'];
    }

    public function getSMOrderPay($tids=array())
    {
        $res=$this->db->select('SUM(payment) as payment')->from(self::ORDERS_TABLE)
            ->where_in('tid',$tids)
            ->get()->row_array();
        return (int)$res['payment'];
    }

    public function getSMPostPay($tids=array())
    {
        $res=$this->db->select('SUM(postFee) as postFee')->from(self::TRADE_TABLE)
            ->where_in('tid',$tids)
            ->get()->row_array();
        return (int)$res['postFee'];
    }

    public function getSMOrderDiscount($tids=array())
    {
        $res=$this->db->select('SUM(discountFee) as discountFee')->from(self::ORDERS_TABLE)
            ->where_in('tid',$tids)
            ->get()->row_array();
        return (int)$res['discountFee'];
    }

    public function formatSMTradesExcel($start,$end)
    {
        $cpstotal = $this->getSMCpsMoney($start,$end);//总佣金

        $invidata = $this->getSMInviMoney($start,$end);
        $invitotal = $invidata['cps'];//邀请佣金
        $invitradenum = $invidata['tradenum'];//邀请订单数
        $invitornum = $invidata['invitor'];//邀请成单人数

        $succseller = $this->getSMSuccNum($start,$end);//成单代言人
        $sellernum = count($succseller);//成单人数
        $tradesnum = $this->getSMTradesNum($start,$end);//订单数

        $validtids = $this->getSMValidTrades($start,$end);//筛选有效订单
        if (empty($validtids)) return false;
        $salesdata = $this->getSMGoodsSales($validtids);
        $goodsdata = $this->getGoodsData();

        $tradetotalpay = $this->getSMTradePay($validtids);//交易实际支付
        $refundtotal = $this->getSMRefundFee($validtids);//主动退款
        $ordertotalpay = $this->getSMOrderPay($validtids);//订单实际支付
        $posttotalpay = $this->getSMPostPay($validtids);//邮费总计
        $orderdiscount = $this->getSMOrderDiscount($validtids);//单品级优惠

        $format = array();
        foreach ($salesdata as $item){
            $format[$item['num_iid']]['num'] = $item['num'];
            $format[$item['num_iid']]['payment'] = $item['payment'];
        }

        $fee = array();
        $totalfee = 0;
        $num = array();

        foreach ($goodsdata as $group){
            $feeitem = 0;
            $numitem = 0;
            foreach ($group['goods'] as $good){
                if (isset($format[$good['num_iid']])){
                    $feeitem += $format[$good['num_iid']]['payment'];
                    $totalfee += (float)$format[$good['num_iid']]['payment'];
                    $numitem += $format[$good['num_iid']]['num'];
                }
            }
            $fee[] = (float)$feeitem/100;
            $num[] = $numitem;
        }

        $discount = (float)($ordertotalpay+$posttotalpay-$tradetotalpay)/100;//店铺级优惠总计
        $posttotalpay = (float)$posttotalpay/100;//邮费总计
        $orderdiscount = (float)$orderdiscount/100;//单品级优惠总计
        $tradetotalpay = (float)$tradetotalpay/100;//支付金额
        $refundtotal = (float)$refundtotal/100;//主动退款
        $realpay = $tradetotalpay-$refundtotal;//实际销售额
        $pct = $tradesnum==0?0:round($tradetotalpay/$tradesnum,2);//客单价
        $cpstotal = (float)$cpstotal/100;//总佣金
        $invitotal = (float)$invitotal/100;//邀请佣金

        //商品销售额...,总佣金,邀请佣金,邮费总计,单品级优惠,店铺级优惠,订单数,邀请订单数,成单人数,邀请成单人数,客单价,支付金额，退款金额，实际销售额
        $fee = array_merge($fee,array($cpstotal,$invitotal,$posttotalpay,$orderdiscount,$discount,$tradesnum,$invitradenum,$sellernum,$invitornum,$pct,$tradetotalpay,$refundtotal,$realpay));
        //商品销量...
        return array("fee"=>$fee,"num"=>$num);
    }
}